package com.hc.sort.server.dao;

import java.sql.*;
import java.util.*;
import com.hc.sort.server.pojo.BusinessType;
import com.hc.sort.utils.JdbcUtil;

public class BusinessTypeDaoImpl implements BusinessTypeDao {
	Connection conn=null;
	PreparedStatement pstmt=null;
	ResultSet rs=null;
	int row=0;

	@Override
	public int add(BusinessType businessType) {
		String sql="insert into t_business_type value(null,?,?,?,?)";
		
		try {
			//获取数据库连接
			conn=JdbcUtil.getConnection();
			pstmt=conn.prepareStatement(sql);
			
			//设置sql的参数
			pstmt.setObject(1, businessType.getCode());
			pstmt.setObject(2, businessType.getName());
			pstmt.setObject(3, businessType.getLimitCount());
			pstmt.setObject(4, businessType.getDesc());
			
			row=pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			JdbcUtil.close(null, pstmt, conn);
		}
		
		return row;
	}

	@Override
	public int delete(int id) {
		String sql="delete from t_business_type where bt_id=?";
		
		try {
			conn=JdbcUtil.getConnection();
			pstmt=conn.prepareStatement(sql);
			pstmt.setObject(1, id);
			
			row=pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			JdbcUtil.close(null, pstmt, conn);
		}
		
		return row;
	}

	@Override
	public int update(BusinessType businessType) {
		String sql="update t_business_type set BT_CODE=?,BT_NAME=?,"
				+ "BT_LIMIT_COUNT=?,BT_DESC=? where bt_id=?";
		
		try {
			conn=JdbcUtil.getConnection();
			pstmt=conn.prepareStatement(sql);
			
			pstmt.setObject(1, businessType.getCode());
			pstmt.setObject(2, businessType.getName());
			pstmt.setObject(3, businessType.getLimitCount());
			pstmt.setObject(4, businessType.getDesc());
			pstmt.setObject(5, businessType.getId());
			
			row=pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			JdbcUtil.close(null, pstmt, conn);
		}
		return row;
	}

	@Override
	public BusinessType get(int id) {
		String sql="select * from t_business_type where bt_id=?";
		try {
			conn=JdbcUtil.getConnection();
			pstmt=conn.prepareStatement(sql);
			
			//设置参数
			pstmt.setObject(1, id);
			rs=pstmt.executeQuery();
			
			while(rs.next()) {
				BusinessType type=new BusinessType();
				type.setId(rs.getInt("BT_ID"));
				type.setCode(rs.getString("BT_CODE"));
				type.setName(rs.getString("BT_NAME"));
				type.setLimitCount(rs.getInt("BT_LIMIT_COUNT"));
				type.setDesc(rs.getString("BT_DESC"));
				
				return type;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			JdbcUtil.close(rs, pstmt, conn);
		}
		return null;
	}

	@Override
	public List<BusinessType> findAll() {
		List<BusinessType> t=new ArrayList<BusinessType>();
		String sql="select * from t_business_type";
		try {
			conn=JdbcUtil.getConnection();
			pstmt=conn.prepareStatement(sql);
			
			//设置参数
			rs=pstmt.executeQuery();
			
			while(rs.next()) {
				BusinessType type=new BusinessType();
				type.setId(rs.getInt("BT_ID"));
				type.setCode(rs.getString("BT_CODE"));
				type.setName(rs.getString("BT_NAME"));
				type.setLimitCount(rs.getInt("BT_LIMIT_COUNT"));
				type.setDesc(rs.getString("BT_DESC"));
				
				t.add(type);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			JdbcUtil.close(rs, pstmt, conn);
		}
		return t;
	}
	
	@Override
	public BusinessType getByCode(String code) {
		String sql="select * from t_business_type where bt_code=?";
		try {
			conn=JdbcUtil.getConnection();
			pstmt=conn.prepareStatement(sql);
			
			//设置参数
			pstmt.setObject(1, code);
			rs=pstmt.executeQuery();
			
			while(rs.next()) {
				BusinessType type=new BusinessType();
				type.setId(rs.getInt("BT_ID"));
				type.setCode(rs.getString("BT_CODE"));
				type.setName(rs.getString("BT_NAME"));
				type.setLimitCount(rs.getInt("BT_LIMIT_COUNT"));
				type.setDesc(rs.getString("BT_DESC"));
				
				return type;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			JdbcUtil.close(rs, pstmt, conn);
		}
		return null;
	}
	
	@Override
	public List<BusinessType> searchByKeyword(String keyWord) {
		List<BusinessType> busList=new ArrayList<BusinessType>();
		
		String sql="select * from t_business_type where bt_code=? or bt_name like ?";
		try {
			conn=JdbcUtil.getConnection();
			pstmt=conn.prepareStatement(sql);
			
			//设置参数
			pstmt.setObject(1, keyWord);
			pstmt.setObject(2, '%' +keyWord+ '%');
			rs=pstmt.executeQuery();
			
			while(rs.next()) {
				BusinessType type=new BusinessType();
				type.setId(rs.getInt("BT_ID"));
				type.setCode(rs.getString("BT_CODE"));
				type.setName(rs.getString("BT_NAME"));
				type.setLimitCount(rs.getInt("BT_LIMIT_COUNT"));
				type.setDesc(rs.getString("BT_DESC"));
				busList.add(type);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			JdbcUtil.close(rs, pstmt, conn);
		}
		return busList;
	}
	
	public static void main(String[] args) {
		BusinessTypeDao typeDao=new BusinessTypeDaoImpl();
		
		//插入-方法1
//		BusinessType type=new BusinessType("C","普通业务",300,"给用户提供满意的服务！！！");
		//插入-方法2
//		BusinessType type=new BusinessType();
//		type.setCode("C");
//		type.setName("普通业务");
//		type.setLimitCount(300);
//		type.setDesc("给用户提供满意的服务！！！");
//		System.out.println(typeDao.add(type));
		
		//删除
//		System.out.println(typeDao.delete(3));
		
		//先从数据库获取，然后更新
//		BusinessType type=typeDao.getByCode("A");
//		System.out.println(type);
//		type.setLimitCount(200);
//		typeDao.update(type);
//		System.out.println(typeDao.get(1));

		//查询全部信息
//		List<BusinessType> list=typeDao.findAll();
//		System.out.println(list);
		
		List busList=typeDao.searchByKeyword("务");
		System.out.println(busList);
				
	}
}
